package jdbc;

import java.sql.*;
import java.util.Scanner;

public class StudentDemo {
    // 数据库配置
    private static final String URL = "jdbc:mysql://localhost:3306/school?useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASS = "123456";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            Scanner scanner = new Scanner(System.in);

            while (true) {
                System.out.println("\n=== 学生管理系统 ===");
                System.out.println("1. 添加学生");
                System.out.println("2. 查询所有学生");
                System.out.println("3. 修改学生年龄");
                System.out.println("4. 退出系统");
                System.out.print("请输入选择：");

                int choice = scanner.nextInt();
                switch (choice) {
                    case 1:
                        addStudent(conn, scanner);
                        break;
                    case 2:
                        showStudents(conn);
                        break;
                    case 3:
                        updateAge(conn, scanner);
                        break;
                    case 4:
                        deleteStudent(conn, scanner);  // 新增删除方法调用
                        break;
                    case 5:
                        System.out.println("系统已退出");
                        return;
                    default:
                        System.out.println("无效输入！");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e);
        }
    }

    // 添加学生
    private static void addStudent(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入学生姓名：");
        String name = scanner.next();
        System.out.print("请输入学生年龄：");
        int age = scanner.nextInt();

        String sql = "INSERT INTO students(name, age) VALUES(?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, age);
            int rows = pstmt.executeUpdate();
            System.out.println(rows > 0 ? "添加成功！" : "添加失败！");
        }
    }

    // 显示所有学生
    private static void showStudents(Connection conn) throws SQLException {
        String sql = "SELECT * FROM students";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("\nID\t姓名\t年龄\t性别");
            while (rs.next()) {
                System.out.printf("%d\t%s\t%d\t%s\n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("gender"));
            }
        }
    }

    // 修改年龄
    private static void updateAge(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要修改的学生ID：");
        int id = scanner.nextInt();
        System.out.print("请输入新年龄：");
        int newAge = scanner.nextInt();

        String sql = "UPDATE students SET age=? WHERE id=?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, newAge);
            pstmt.setInt(2, id);
            int rows = pstmt.executeUpdate();
            /*
            执行SQL更新语句，并返回受影响的行数。具体功能如下：
            调用 PreparedStatement 的 executeUpdate 方法，将 SQL 更新语句发送到数据库执行。
            返回值 rows 表示受影响的行数，若返回值大于0，则表示修改成功；否则表示没有匹配的记录。
             */
            System.out.println(rows > 0 ? "修改成功！" : "学生不存在！");
        }
    }
    // 新增删除学生方法
    private static void deleteStudent(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要删除的学生ID：");
        int id = scanner.nextInt();

        String sql = "DELETE FROM students WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            int rows = pstmt.executeUpdate();

            if(rows > 0) {
                System.out.println("成功删除学生记录！");
            } else {
                System.out.println("没有找到该学生，删除失败！");
            }
        }
    }
    /**
     * 处理SQL异常的私有静态方法
     * 该方法被调用以处理与数据库相关的错误，特别是SQL异常
     * 它打印出错误信息，错误代码和SQL状态，并针对特定的错误代码提供更详细的错误信息
     *
     * @param e SQLException实例，包含数据库错误的详细信息
     */
    private static void handleSQLException(SQLException e) {
        // 打印数据库错误信息
        System.err.println("数据库错误：" + e.getMessage());
        // 打印数据库错误代码
        System.err.println("错误代码：" + e.getErrorCode());
        // 打印SQL状态代码
        System.err.println("SQL状态：" + e.getSQLState());

        // 检查特定的错误代码，并提供更具体的错误
    }
}